﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using Models;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class IteracoesDAL
    {
        private string connectionString = ConfigurationManager.ConnectionStrings["ScrumDB"].ConnectionString;

        public List<Iteracoes> Listar(int CodigoProjeto)
        {

            List<Iteracoes> lista = new List<Iteracoes>();

            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT CodigoIteracao, CodigoProjeto, NomeIteracao, DataInicioIteracao, DataTerminoIteracao FROM Iteracoes WHERE CodigoProjeto = @CodigoProjeto";

            // Tratar parametro de entrada
            SqlParameter parameter = command.CreateParameter();
            parameter.ParameterName = "CodigoProjeto";
            parameter.Value = CodigoProjeto;
            parameter.DbType = DbType.Int32;
            command.Parameters.Add(parameter);


            connection.Open();
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                lista.Add(
                    new Iteracoes
                    {
                        CodigoIteracao = reader.GetInt32(0),
                        CodigoProjeto = reader.GetInt32(1),
                        NomeIteracao = reader.GetString(2),
                        DataInicioIteracao = reader.GetDateTime(3),
                        DataTerminoIteracao = reader.GetDateTime(4)
                    }
                );
            }
            reader.Close();
            connection.Close();

            return lista;


        }

        public Iteracoes Ler(int CodigoIteracao)
        {

            Iteracoes instancia = null;

            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT CodigoIteracao, CodigoProjeto, NomeIteracao, DataInicioIteracao, DataTerminoIteracao FROM Iteracoes WHERE CodigoIteracao = @CodigoIteracao";

            // Tratar parametro de entrada
            SqlParameter parameter = command.CreateParameter();
            parameter.ParameterName = "CodigoIteracao";
            parameter.Value = CodigoIteracao;
            parameter.DbType = DbType.Int32;
            command.Parameters.Add(parameter);


            connection.Open();
            SqlDataReader reader = command.ExecuteReader();
            if (reader.Read())
            {
                instancia =
                    new Iteracoes
                    {

                        CodigoIteracao = reader.GetInt32(0),
                        CodigoProjeto = reader.GetInt32(1),
                        NomeIteracao = reader.GetString(2),
                        DataInicioIteracao = reader.GetDateTime(3),
                        DataTerminoIteracao = reader.GetDateTime(4)

                    };

            }
            reader.Close();
            connection.Close();
            return instancia;
        }

        public void Incluir(Iteracoes instancia)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand command = connection.CreateCommand();
            command.CommandText =
                @"INSERT INTO Iteracoes (CodigoProjeto, NomeIteracao, DataInicioIteracao, DataTerminoIteracao) VALUES (@CodigoProjeto, @NomeIteracao, @DataInicioIteracao, @DataTerminoIteracao)";
         /*   SqlParameter parameter1 = command.CreateParameter();
            parameter1.ParameterName = "CodigoIteracao";
            parameter1.Value = instancia.CodigoIteracao;
            parameter1.DbType = DbType.Int32;
            command.Parameters.Add(parameter1);
            */
            SqlParameter parameter2 = command.CreateParameter();
            parameter2.ParameterName = "CodigoProjeto";
            parameter2.Value = instancia.CodigoProjeto;
            parameter2.DbType = DbType.Int32;
            command.Parameters.Add(parameter2);

            SqlParameter parameter3 = command.CreateParameter();
            parameter3.ParameterName = "NomeIteracao";
            parameter3.Value = instancia.NomeIteracao;
            parameter3.DbType = DbType.String;
            command.Parameters.Add(parameter3);

            SqlParameter parameter4 = command.CreateParameter();
            parameter4.ParameterName = "DataInicioIteracao";
            parameter4.Value = instancia.DataInicioIteracao;
            parameter4.DbType = DbType.DateTime;
            command.Parameters.Add(parameter4);

            SqlParameter parameter5 = command.CreateParameter();
            parameter5.ParameterName = "DataTerminoIteracao";
            parameter5.Value = instancia.DataTerminoIteracao;
            parameter5.DbType = DbType.DateTime;
            command.Parameters.Add(parameter5);

            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public void Alterar(Iteracoes instancia)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand command = connection.CreateCommand();
            command.CommandText =
                @"UPDATE Iteracoes SET CodigoProjeto=@CodigoProjeto, NomeIteracao=@NomeIteracao, DataInicioIteracao=@DataInicioIteracao, DataTerminoIteracao=@DataTerminoIteracao
                                      WHERE CodigoIteracao=@CodigoIteracao";

            SqlParameter parameter1 = command.CreateParameter();
            parameter1.ParameterName = "CodigoProjeto";
            parameter1.Value = instancia.CodigoProjeto;
            parameter1.DbType = DbType.Int32;
            command.Parameters.Add(parameter1);

            SqlParameter parameter2 = command.CreateParameter();
            parameter2.ParameterName = "NomeIteracao";
            parameter2.Value = instancia.NomeIteracao;
            parameter2.DbType = DbType.String;
            command.Parameters.Add(parameter2);

            SqlParameter parameter3 = command.CreateParameter();
            parameter3.ParameterName = "DataInicioIteracao";
            parameter3.Value = instancia.DataInicioIteracao;
            parameter3.DbType = DbType.DateTime;
            command.Parameters.Add(parameter3);

            SqlParameter parameter4 = command.CreateParameter();
            parameter4.ParameterName = "DataTerminoIteracao";
            parameter4.Value = instancia.DataTerminoIteracao;
            parameter4.DbType = DbType.DateTime;
            command.Parameters.Add(parameter4);

            SqlParameter parameter5 = command.CreateParameter();
            parameter5.ParameterName = "CodigoIteracao";
            parameter5.Value = instancia.CodigoIteracao;
            parameter5.DbType = DbType.Int32;
            command.Parameters.Add(parameter5);

            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();

        }

        public void Excluir(int CodigoIteracao)
        {

            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand command = connection.CreateCommand();
            command.CommandText =
                @"DELETE FROM Iteracoes WHERE CodigoIteracao = @CodigoIteracao";

            // Tratar parametro de entrada
            SqlParameter parameter = command.CreateParameter();
            parameter.ParameterName = "CodigoIteracao";
            parameter.Value = CodigoIteracao;
            parameter.DbType = DbType.Int32;
            command.Parameters.Add(parameter);


            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }


        public int CodigoProjeto { get; set; }
    }

}
